package cn.com.util;

import cn.com.entity.CompanyMember;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by wangye on 2016/11/30.
 */
public class CompanyMemberUtil {
    public static List<CompanyMember> getAllCompanyMemberFormExcel(String filePath){

        List<CompanyMember> companyMemberList = new ArrayList<CompanyMember>();

        //String filePath = "E:\\123.xlsx";

        //判断是否为excel类型文件
        if(!filePath.endsWith(".xls")&&!filePath.endsWith(".xlsx"))
        {
            System.out.println("文件不是excel类型");
        }

        FileInputStream fis =null;
        Workbook wookbook = null;

            try{
                //获取一个绝对地址的流
                fis = new FileInputStream(filePath);
            }catch(Exception e){
                e.printStackTrace();
            }

            try{
                //2003版本的excel，用.xls结尾
                wookbook = new HSSFWorkbook(fis);//得到工作簿

            }catch (Exception ex){
                //ex.printlnStackTrace();
                try{
                    //2007版本的excel，用.xlsx结尾

                    wookbook = new XSSFWorkbook(fis);//得到工作簿
                } catch (IOException e){
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }

        //得到一个工作表
        Sheet sheet = wookbook.getSheetAt(0);

        //获得表头
        Row rowHead = sheet.getRow(0);

        //判断表头是否正确
        if(rowHead.getPhysicalNumberOfCells() !=9)
        {
            System.out.println("表头的数量不对!");
        }

        //获得数据的总行数
        int totalRowNum = sheet.getLastRowNum();

        CompanyMember companyMember=null;
        //获取所有的 用户
        for (int i =1 ; i <totalRowNum;i++){
            Row row = sheet.getRow(i);
            //行为空 结束
            if(row==null){
                System.out.println("行"+i+"为空");
                return companyMemberList;
            }
            companyMember= new CompanyMember();
            //获得获得第i行第0列的 String类型对象  用户名
            Cell cell = row.getCell((short) 0);
            if(cell==null){
                System.out.println("用户名不能为空"+i);
                continue;
            }
            companyMember.setUserName(cell.getStringCellValue().toString());

            //获得一个数字类型的数据  用户类别
            cell = row.getCell((short) 1);
            if(cell==null){
                System.out.println("用户类型不能为空"+i);
                continue;
            }
             companyMember.setUserType ((int) cell.getNumericCellValue());

            //获得一个性别
            cell = row.getCell((short) 2);
            if(cell==null){
                System.out.println("性别不能为空"+i);
                continue;
            }
           companyMember.setSex((int)cell.getNumericCellValue());


            //获得一个设备号
            companyMember.setImei(null);
            cell = row.getCell((short) 3);
            if(companyMember.getUserType()==1) {
                if (cell == null) {
                    System.out.println("imei号不能为空！"+i);
                    continue;
                } else
                    companyMember.setImei(cell.getStringCellValue());
            }
            //获得一个 手机号
            cell = row.getCell((short) 4);
            if(cell==null){
                System.out.println("手机号不能为空"+i);
                continue;
            }
            companyMember.setMobile( (long)cell.getNumericCellValue()+"");
            //获得一个状态
            cell = row.getCell((short) 5);
            if(cell==null){
                System.out.println("状态不能为空"+i);
                continue;
            }
            companyMember.setStatus( (int) cell.getNumericCellValue());
            //获得 群组列表
            cell = row.getCell((short) 6);
            if(cell==null)
                companyMember.setGroupIds( null);
            else
                companyMember.setGroupIds( cell.getStringCellValue());

            //获得 默认群组
            cell = row.getCell((short) 7);
            if(cell==null)
                companyMember.setDefaultGroupId(null);
            else
                companyMember.setDefaultGroupId( (int) cell.getNumericCellValue());
            //获得 权限
            cell = row.getCell((short) 8);
            if(cell==null)
                companyMember.setPermissions(null);
            else
                companyMember.setPermissions(cell.getStringCellValue());

            //默认状态
            //companyMember.setPasswd("123456");//默认值  到时候读取 常量

            companyMemberList.add(companyMember);
        }
        return companyMemberList;
    }
}
